import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import cn.hutool.core.util.StrUtil;

/**
 * 读取excel并组装SQL，同时输出到txt文件
 */
public class ExcelToFileWriter {

    public static void main(String[] args) {

        String format = "({}, '{}', '{}', {}, 'xx@xx.com', 'xx', '0', 'system', '2025-06-09 18:24:39', '2025-06-09 18:24:39', '')";

        List<List<String>> dataList = readExcel("C:\\Users\\ThinkPad\\Desktop\\areacode.xlsx");

        // 要写入的字符串内容
        String content = "INSERT INTO sec_department\n" +
                "(DEPARTMENT_ID, DEPARTMENT_NAME, DEPARTMENT_DESC, PARENT_ID, EMAIL, ADDRESS, ADD_SUB, CREATE_USER, CREATE_DATE, LAST_UPDATE_DATE, `DOMAIN`) VALUES\n";

        int deptId = 100000;
        for (int i = 0; i < dataList.size(); i++) {
            if (i == 0) {
                continue;
            }
            List<String> data = dataList.get(i);
            String value = StrUtil.format(format, deptId, data.get(2), data.get(1), Double.valueOf(data.get(0)).intValue());
            deptId++;
            if (i == dataList.size() - 1) {
                content += value + ";";
            } else {
                content += value + ",\n";
            }
        }

        // 文件路径
        String filePath = "C:\\Users\\ThinkPad\\Desktop\\output.txt";

        // 使用try-with-resources自动关闭资源
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            // 写入字符串到文件
            writer.write(content);

            System.out.println("字符串已成功写入文件: " + filePath);
        } catch (IOException e) {
            System.err.println("写入文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static List<List<String>> readExcel(String filePath) {
        List<List<String>> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = WorkbookFactory.create(fis)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);

            // 遍历所有行
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                List<String> rowData = new ArrayList<>();

                // 遍历行中的所有单元格
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    // 根据单元格类型获取值
                    switch (cell.getCellType()) {
                        case STRING:
                            rowData.add(cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                rowData.add(cell.getDateCellValue().toString());
                            } else {
                                rowData.add(String.valueOf(cell.getNumericCellValue()));
                            }
                            break;
                        case BOOLEAN:
                            rowData.add(String.valueOf(cell.getBooleanCellValue()));
                            break;
                        case FORMULA:
                            rowData.add(cell.getCellFormula());
                            break;
                        default:
                            rowData.add("");
                    }
                }
                data.add(rowData);
            }
        } catch (IOException e) {
            System.err.println("读取Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }

        return data;
    }

}
